<?php
$PageSecurity = 6;
include_once ('includes/session.inc');

$pageSize = 10;
if (isset ( $_GET ['action'] )) {
    set_time_limit ( 0 );
    $action = $_GET ['action'];
} else {
    $action = isset ( $_POST ['action'] ) ? $_POST ['action'] : 'get';
}

if ($action == 'get') {
    // Get datas
    $total = 0;
    $limit = isset ( $_POST ['limit'] ) ? $_POST ['limit'] : $pageSize;
    $start = isset ( $_POST ['start'] ) ? $_POST ['start'] : 0;
    $conds = array ();
    getConditions ( $conds, 'locked', '=', '0' );
    getConditions ( $conds, 'song_number', 'like', $_POST ['srch_song_number'] );
    getConditions ( $conds, 'song_name', 'like', $_POST ['srch_song_name'] );
    getConditions ( $conds, 'type_id', '=', $_POST ['srch_type_id'] );
    getConditions ( $conds, 'from_date', '>=', $_POST ['srch_from_update_date'] );
    getConditions ( $conds, 'to_date', '<=', $_POST ['srch_to_update_date'] );
    getConditions ( $conds, 'singer_id', '=', $_POST ['srch_singer_id'] );
    getConditions ( $conds, 'composer_id', '=', $_POST ['srch_composer_id'] );
    getConditions ( $conds, 'partner_id', '=', $_POST ['srch_partner_id'] );
    if ($_POST ['srch_song_singer_authority'] == 'true')
        getConditions ( $conds, 'song_singer_authority', '=', 1 );
    if ($_POST ['srch_song_composer_authority'] == 'true')
        getConditions ( $conds, 'song_composer_authority', '=', 1 );
    getConditions ( $conds, 'song_release_authority', '=', $_POST ['srch_song_release_authority'] );
    // if($_POST['srch_song_release_authority'] == 'true') getConditions($conds, 'song_release_authority', '=', 1);

    // Execute
    $result = _single ( 'api/song' )->getSongPaginates ( $limit, $start, $total, $conds );

    // Response
    $data = '{"success":true';
    $data .= ',"data":[';
    $haveData = false;

    while ( $row = DB_fetch_array ( $result ) ) {
        $haveData = true;
        $data .= sprintf ( '{"song_id":"%s","type_id":"%s","partner_id":"%s","singer_id":"%s",
            "composer_id":"%s","song_number":"%s","song_name":"%s","song_singer_sing":"%s","song_unsigned_name":"%s","song_price":"%s",
            "song_singer_authority":"%s","song_composer_authority":"%s","song_release_authority":"%s",
            "singer_name":"%s", "composer_name":"%s", "created_date":"%s", "ended_date":"%s"},', $row ['song_id'], $row ['type_id'], $row ['partner_id'], $row ['singer_id'], $row ['composer_id'], $row ['song_number'], $row ['song_name'], $row ['song_singer_sing'], $row ['song_unsigned_name'], $row ['song_price'], ($row ['song_singer_authority'] == 0) ? '' : 1, ($row ['song_composer_authority'] == 0) ? '' : 1, ($row ['song_release_authority'] == 0) ? '' : 1, $row ['singer_name'], $row ['composer_name'], $row ['created_date'], $row ['ended_date'] );
    }

    if ($haveData) {
        $data = substr ( $data, 0, strlen ( $data ) - 1 );
    }

    $data .= '],"count":"' . json_encode ( $total ) . '"}';
    echo $data;
} elseif ($action == 'getListSongType') {
    $result = _single ( 'api/type' )->GetAllSongType ( $_SESSION ['UserID'], $_SESSION ['Password'] );

    $data = '{"success":true';
    $data .= ',"data":[';
    $haveData = false;

    while ( $row = DB_fetch_array ( $result ) ) {
        $haveData = true;
        $data .= sprintf ( '{"type_id":"%s","type_number":"%s","type_name":"%s"},', $row ['type_id'], $row ['type_number'], $row ['type_name'] );
    }

    if ($haveData) {
        $data = substr ( $data, 0, strlen ( $data ) - 1 );
    }

    $data .= '],"count":"' . json_encode ( $total ) . '"}';
    echo $data;
} elseif ($action == 'getListPartner') {
    $result = _single ( 'api/partner' )->GetAllPartner ( $_SESSION ['UserID'], $_SESSION ['Password'] );

    $data = '{"success":true';
    $data .= ',"data":[';
    $haveData = false;

    while ( $row = DB_fetch_array ( $result ) ) {
        $haveData = true;
        $data .= sprintf ( '{"partner_id":"%s","partner_number":"%s","partner_name":"%s"},', $row ['partner_id'], $row ['partner_number'], $row ['partner_name'] );
    }

    if ($haveData) {
        $data = substr ( $data, 0, strlen ( $data ) - 1 );
    }

    $data .= '],"count":"' . json_encode ( $total ) . '"}';
    echo $data;
} elseif ($action == 'getListObject') {
    $result = _single ( 'api/object' )->GetAllObject ( $_SESSION ['UserID'], $_SESSION ['Password'] );

    $data = '{"success":true';
    $data .= ',"data":[';
    $haveData = false;

    while ( $row = DB_fetch_array ( $result ) ) {
        $haveData = true;
        $data .= sprintf ( '{"object_id":"%s","object_number":"%s","object_name":"%s","object_type":"%s"},', $row ['object_id'], $row ['object_number'], $row ['object_name'], $row ['object_type'] );
    }

    if ($haveData) {
        $data = substr ( $data, 0, strlen ( $data ) - 1 );
    }

    $data .= '],"count":"' . json_encode ( $total ) . '"}';
    echo $data;
} elseif ($action == 'getListSinger') {
    $total = 0;
    $limit = isset ( $_POST ['limit'] ) ? $_POST ['limit'] : $pageSize;
    $start = isset ( $_POST ['start'] ) ? $_POST ['start'] : 0;

    $condition = array ();
    if (! empty ( $_POST ['query'] ))
        $condition ['object_name'] = "N'%" . DB_escape_string ( $_POST ['query'] ) . "%'";
    $result = _single ( 'api/object' )->loadObjects ( array (
            1,
            3
    ), $limit, $start, $total, $_SESSION ['UserID'], $_SESSION ['Password'], $condition );

    $data = '{"success":true';
    $data .= ',"data":[';
    $haveData = false;

    while ( $row = DB_fetch_array ( $result ) ) {
        $haveData = true;
        $data .= sprintf ( '{"object_id":"%s","object_number":"%s","object_name":"%s","object_type":"%s"},', $row ['object_id'], $row ['object_number'], $row ['object_name'], $row ['object_type'] );
    }

    if ($haveData) {
        $data = substr ( $data, 0, strlen ( $data ) - 1 );
    }

    $data .= '],"count":"' . json_encode ( $total ) . '"}';
    echo $data;
} elseif ($action == 'getListComposer') {
    $total = 0;
    $limit = isset ( $_POST ['limit'] ) ? $_POST ['limit'] : $pageSize;
    $start = isset ( $_POST ['start'] ) ? $_POST ['start'] : 0;

    $condition = array ();
    if (! empty ( $_POST ['query'] ))
        $condition ['object_name'] = "N'%" . DB_escape_string ( trim ( $_POST ['query'] ) ) . "%'";
    $result = _single ( 'api/object' )->loadObjects ( array (
            2,
            3
    ), $limit, $start, $total, $_SESSION ['UserID'], $_SESSION ['Password'], $condition );

    $data = '{"success":true';
    $data .= ',"data":[';
    $haveData = false;

    while ( $row = DB_fetch_array ( $result ) ) {
        $haveData = true;
        $data .= sprintf ( '{"object_id":"%s","object_number":"%s","object_name":"%s","object_type":"%s"},', $row ['object_id'], $row ['object_number'], $row ['object_name'], $row ['object_type'] );
    }

    if ($haveData) {
        $data = substr ( $data, 0, strlen ( $data ) - 1 );
    }

    $data .= '],"count":"' . json_encode ( $total ) . '"}';
    echo $data;
} elseif ($action == 'insert') {
    // Get datas:
    $data = array ();
    if (! empty ( $_POST ['type_id'] ))
        $data ['type_id'] = $_POST ['type_id'];
    if (! empty ( $_POST ['partner_id'] ))
        $data ['partner_id'] = $_POST ['partner_id'];
    if (! empty ( $_POST ['singer_id'] ))
        $data ['singer_id'] = $_POST ['singer_id'];
    if (! empty ( $_POST ['composer_id'] ))
        $data ['composer_id'] = $_POST ['composer_id'];
    if (! empty ( $_POST ['song_number'] ))
        $data ['song_number'] = DB_escape_string ( trim ( $_POST ['song_number'] ) );
    if (! empty ( $_POST ['song_name'] ))
        $data ['song_name'] = DB_escape_string ( trim ( $_POST ['song_name'] ) );
    if (! empty ( $_POST ['song_singer_sing'] ))
        $data ['song_singer_sing'] = DB_escape_string ( trim ( $_POST ['song_singer_sing'] ) );
        // if(!empty($_POST['song_price'])) $data['song_price'] = trim($_POST['song_price']);
    if (! empty ( $_POST ['created_date'] ))
        $data ['created_date'] = DB_escape_string ( trim ( $_POST ['created_date'] ) );
    if (! empty ( $_POST ['song_singer_authority'] ))
        $data ['song_singer_authority'] = $_POST ['song_singer_authority'];
    if (! empty ( $_POST ['song_composer_authority'] ))
        $data ['song_composer_authority'] = $_POST ['song_composer_authority'];
    if (! empty ( $_POST ['song_release_authority'] ))
        $data ['song_release_authority'] = $_POST ['song_release_authority'];

    $result = array ();
    $Errors = array ();
    $db = db ( $_SESSION ['UserID'], $_SESSION ['Password'] );
    $Errors = _single ( 'api/song' )->VerifySongNumberExists ( $data ['song_number'], $data ['partner_id'], sizeof ( $Errors ), $Errors, $db );

    $result ['error'] = 0;
    if (sizeof ( $Errors ) != 0) {
        $result ['error'] = 1;
    } else {
        $result ['id'] = _single ( 'api/song' )->insert ( $data, $_SESSION ['UserID'], $_SESSION ['Password'] );
    }
    ;
    echo '{"success":true,"data":' . json_encode ( $result ) . '}';
} elseif ($action == 'update') {
    $data = array ();
    if (! empty ( $_POST ['field'] ))
        $data ['field'] = $_POST ['field'];
    if (! empty ( $_POST ['value'] ))
        $data ['value'] = DB_escape_string ( trim ( $_POST ['value'] ) );
    if (! empty ( $_POST ['song_id'] ))
        $data ['song_id'] = $_POST ['song_id'];
    if (! empty ( $_POST ['partner_id'] ))
        $data ['partner_id'] = $_POST ['partner_id'];
        // Execute
    $result = array ();
    $result ['error'] = 0;
    if ($_POST ['field'] == 'song_number') {
        $Errors = array ();
        $db = db ( $_SESSION ['UserID'], $_SESSION ['Password'] );
        $Errors = _single ( 'api/song' )->VerifySongNumberExists ( $data ['value'], $data ['partner_id'], sizeof ( $Errors ), $Errors, $db );
        if (sizeof ( $Errors ) != 0) {
            $result ['error'] = 1;
        } else {
            $result ['data'] = _single ( 'api/song' )->update ( $data, $_SESSION ['UserID'], $_SESSION ['Password'] );
        }
    } else {
        $result ['data'] = _single ( 'api/song' )->update ( $data, $_SESSION ['UserID'], $_SESSION ['Password'] );
    }
    // Response
    echo '{"success":true ,"data":' . json_encode ( $result ) . '}';
} elseif ($action == 'delete') {
    $data = array ();
    $delete_rs = _single ( 'api/song' )->delete ( $_POST ['song_id'], $_SESSION ['UserID'], $_SESSION ['Password'] );
    if ($delete_rs === false) {
        echo '{"success":false ,"data":' . json_encode ( null ) . '}';
    } else {
        echo '{"success":true ,"data":' . json_encode ( $_POST ['song_id'] ) . '}';
    }
} elseif ($action == 'export') {

    $DEFAULT_EXPORT_FILE_NAME = 'DanhSachKhoBaiHat';
    $DEFAULT_DIR_UPLOAD = 'tmp/exported/';
    $DEFAULT_EXCEL_TYPE = 'Excel5'; // default Excel 2003 - Excel5,Excel2007

    $objPHPExcel = new Api_Excel ( $DEFAULT_EXPORT_FILE_NAME . '_' . date ( 'YmdHis' ), $DEFAULT_DIR_UPLOAD, $DEFAULT_EXCEL_TYPE );
    $objPHPExcel->setActiveSheet ( 0 );

    $aligment_HorCent_VerCent = array (
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
    );

    $aligment_HorCent_VerBottom = array (
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            'vertical' => PHPExcel_Style_Alignment::VERTICAL_BOTTOM
    );

    $border_thin = array (
            'style' => PHPExcel_Style_Border::BORDER_THIN
    );
    $border_thin_ALL_title = array (
            'borders' => array (
                    'top' => $border_thin,
                    'right' => $border_thin,
                    'bottom' => $border_thin,
                    'left' => $border_thin
            ),
            'alignment' => $aligment_HorCent_VerCent,
            'font' => array (
                    'bold' => true,
                    'size' => 12
            )
    );

    $border_thin_ALL = array (
            'borders' => array (
                    'top' => $border_thin,
                    'right' => $border_thin,
                    'bottom' => $border_thin,
                    'left' => $border_thin
            ),
            'alignment' => $aligment_HorCent_VerBottom,
            'font' => array (
                    'bold' => false,
                    'size' => 12
            )
    );

    // set default style
    $objPHPExcel->setPageSetup ( 85, PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4, PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT );
    $objPHPExcel->setDefaultStyle ( 'Times New Roman', 10 );
    // $objPHPExcel->setFreezePane("E13");
    $objPHPExcel->setPageMargins ( 0.5, 0.25, 0, 0.75 );
    $objPHPExcel->setHorizontalCentered ( true );

    $objPHPExcel->setColumnWidth ( "A", 0.1 )->setColumnWidth ( "B", 8 )->setColumnWidth ( "C", 18 )->setColumnWidth ( "D", 35 )->setColumnWidth ( "E", 35 )->setColumnWidth ( "F", 10 )->setColumnWidth ( "G", 25 )->setColumnWidth ( "H", 10 )->setColumnWidth ( "I", 25 )->setColumnWidth ( "J", 12 )->setColumnWidth ( "K", 12 )->setRowHeight ( 2, 30 );
    $objPHPExcel->mergeCells ( "B1", "K1" )->setText ( "B1", "Danh sách bài hát tổng VGT tới ngày " . date ( 'd-m-Y' ), false, false, false, 14 )->setCellHorizontalAllignment ( "B1", 'center' )->setText ( "B2", "Stt" )->setText ( "C2", "Mã số" )->setText ( "D2", "Tên bài hát có dấu" )->setText ( "E2", "Tên bài hát không dấu" )->setText ( "F2", "Mã ca sĩ" )->setText ( "G2", "Tên ca sĩ" )->setText ( "H2", "Mã nhạc sĩ" )->setText ( "I2", "Tên nhạc sĩ" )->setText ( "J2", "Quyền ghi âm" )->setText ( "K2", "Quyền tác giả" )->setWrapText ( "B2" )->setWrapText ( "C2" )->setWrapText ( "D2" )->setWrapText ( "E2" )->setWrapText ( "F2" )->setWrapText ( "G2" )->setWrapText ( "H2" )->setWrapText ( "I2" )->setWrapText ( "J2" )->setWrapText ( "K2" )->setDuplicateStyleArray ( $border_thin_ALL_title, "B1", "K1" )->setDuplicateStyleArray ( $border_thin_ALL_title, "A2", "A2" )->setDuplicateStyleArray ( $border_thin_ALL_title, "B2", "B2" )->setDuplicateStyleArray ( $border_thin_ALL_title, "C2", "C2" )->setDuplicateStyleArray ( $border_thin_ALL_title, "D2", "D2" )->setDuplicateStyleArray ( $border_thin_ALL_title, "E2", "E2" )->setDuplicateStyleArray ( $border_thin_ALL_title, "F2", "F2" )->setDuplicateStyleArray ( $border_thin_ALL_title, "G2", "G2" )->setDuplicateStyleArray ( $border_thin_ALL_title, "H2", "H2" )->setDuplicateStyleArray ( $border_thin_ALL_title, "I2", "I2" )->setDuplicateStyleArray ( $border_thin_ALL_title, "J2", "J2" )->setDuplicateStyleArray ( $border_thin_ALL_title, "K2", "K2" );
    $total = 0;
    $conds = array ();
    getConditions ( $conds, 'locked', '=', '0' );
    getConditions ( $conds, 'song_number', 'like', $_GET ['sb'] );
    getConditions ( $conds, 'song_name', 'like', $_GET ['sn'] );
    getConditions ( $conds, 'type_id', '=', $_GET ['st'] );
    getConditions ( $conds, 'from_date', '>=', $_GET ['fcd'] );
    getConditions ( $conds, 'to_date', '<=', $_GET ['tcd'] );
    getConditions ( $conds, 'singer_id', '=', $_GET ['ss'] );
    getConditions ( $conds, 'composer_id', '=', $_GET ['sc'] );
    getConditions ( $conds, 'partner_id', '=', $_GET ['pi'] );
    if ($_GET ['srch_song_singer_authority'] == 'true')
        getConditions ( $conds, 'ssa', '=', 1 );
    if ($_GET ['srch_song_composer_authority'] == 'true')
        getConditions ( $conds, 'sca', '=', 1 );
        // if($_GET['srch_song_release_authority'] == 'true') getConditions($conds, 'song_release_authority', '=', 1);

    // Execute
    $result = _single ( 'api/song' )->getSongPaginates ( null, null, $total, $conds );
    $Position = 3;
    $Stt = 1;
    while ( $row = DB_fetch_array ( $result ) ) {

        $objPHPExcel->setDuplicateStyleArray ( $border_thin_ALL, "B" . ($Position), "B$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "C" . ($Position), "C$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "D" . ($Position), "D$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "E" . ($Position), "E$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "F" . ($Position), "F$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "G" . ($Position), "G$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "H" . ($Position), "H$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "I" . ($Position), "I$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "J" . ($Position), "J$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "K" . ($Position), "K$Position" )->setText ( "B$Position", $Stt )->setText ( "C$Position", $row ['song_number'] )->setText ( "D$Position", $row ['song_name'] )->setText ( "E$Position", $row ['song_unsigned_name'] )->setText ( "F$Position", $row ['singer_number'] )->setText ( "G$Position", $row ['singer_name'] )->setText ( "H$Position", $row ['composer_number'] )->setText ( "I$Position", $row ['composer_name'] )->setText ( "J$Position", $row ['song_singer_authority'] == 1 ? $row ['song_singer_authority'] : '' )->setText ( "K$Position", $row ['song_composer_authority'] == 1 ? $row ['song_composer_authority'] : '' )->setCellHorizontalAllignment ( "D" . ($Position), 'left' )->setCellHorizontalAllignment ( "E" . ($Position), 'left' )->setCellHorizontalAllignment ( "G" . ($Position), 'left' )->setCellHorizontalAllignment ( "I" . ($Position), 'left' );
        $Stt ++;
        $Position ++;
    }
    $objPHPExcel->outputToBrowser ();
    exit ();
} elseif ($action == 'unExecuteSong') {
    $data = array ();
    $executeSongRs = _single ( 'api/song' )->unExecuteSong ( $_POST ['ended_date'], $_SESSION ['UserID'], $_SESSION ['Password'] );
    if ($executeSongRs === false) {
        echo '{"success":true ,"data":false}';
    } else {
        echo '{"success":true ,"data":true}';
    }
}
function getConditions(&$conditions, $field, $relation, $value) {
    if (isset ( $value ) && $field == 'song_release_authority' && $value != '' && $value != null) {
        array_push ( $conditions, array (
                'field' => $field,
                'relation' => $relation,
                'value' => $value
        ) );
    } elseif (isset ( $value ) && (! empty ( $value ) || $field == 'locked')) {
        $value = ($relation == 'like') ? "N'%" . DB_escape_string ( trim ( $value ) ) . "%'" : DB_escape_string ( trim ( $value ) );
        if ($field == 'from_date' || $field == 'to_date') {
            $field = 'created_date';
            $value = '\'' . $value . '\'';
        }
        ;
        array_push ( $conditions, array (
                'field' => $field,
                'relation' => $relation,
                'value' => $value
        ) );
    }
    ;
}
;
?>
